{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# White Christmas\n",
    "\n",
    "## HadCET\n",
    "The [HadCET](http://www.metoffice.gov.uk/hadobs/hadcet/) data is \"the longest available instrumental record of temperature in the world\", currently available from the UK Met Office. It provides the daily mean temperature for the centre of England since 1772.\n",
    "\n",
    "From that table you can see that the mean daily temperature for 1st January 2001 was recorded as 40 meaning 4.0°C, the temperature for 2nd January 2001 has 77 which translates to 7.7°C\n",
    "\n",
    "The February temperatures are in the m2 column, notice that -999 has been entered for invalid cells such as 31 Feb 2001.\n",
    "\n",
    "```\n",
    "+------+----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+\n",
    "| yr   | dy |  m1 |  m2 |  m3 |  m4 |  m5 |  m6 |  m7 |  m8 |  m9 | m10 | m11 | m12 |\n",
    "+------+----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+\n",
    "| 2001 |  1 |  40 |  11 |   9 | 112 |  87 | 131 | 185 | 180 | 150 | 151 |  91 |  98 |\n",
    "| 2001 |  2 |  77 |  32 |  -4 | 114 |  99 | 126 | 207 | 163 | 168 | 143 |  99 |  29 |\n",
    "| 2001 |  3 |  52 |  66 |  -3 |  84 | 105 | 100 | 209 | 166 | 153 | 142 |  87 |  55 |\n",
    "| 2001 |  4 |  50 |  57 |   5 |  69 |  72 | 130 | 219 | 151 | 134 | 139 |  90 |  73 |\n",
    "| 2001 |  5 |  47 |  68 |  12 |  71 |  82 | 136 | 217 | 161 | 139 | 159 |  75 |  82 |\n",
    "| 2001 |  6 |  43 |  83 |  57 |  90 |  76 | 135 | 201 | 173 | 141 | 142 |  95 |  46 |\n",
    "| 2001 |  7 |  49 |  85 |  86 |  69 |  90 | 110 | 185 | 161 | 153 | 128 | 112 |  62 |\n",
    "| 2001 |  8 |  51 |  34 |  87 |  71 |  93 | 102 | 163 | 155 | 147 | 127 |  50 |  40 |\n",
    "| 2001 |  9 |  22 |  21 |  90 | 101 | 102 | 101 | 157 | 141 | 121 | 132 |  33 |  16 |\n",
    "| 2001 | 10 |  23 |  51 | 102 |  96 | 147 | 110 | 160 | 152 | 119 | 125 |  44 |  19 |\n",
    "| 2001 | 11 |  29 |  81 | 101 |  72 | 172 | 106 | 141 | 152 | 142 | 150 |  78 |  20 |\n",
    "| 2001 | 12 |  40 |  79 |  60 |  74 | 168 | 138 | 144 | 171 | 142 | 153 | 101 |  46 |\n",
    "| 2001 | 13 |  31 |  40 |  57 |  57 | 165 | 148 | 143 | 195 | 124 | 160 |  53 |  47 |\n",
    "| 2001 | 14 |  24 |  35 |  64 |  75 | 118 | 144 | 132 | 205 | 124 | 155 |  40 |  15 |\n",
    "| 2001 | 15 |  18 |  44 |  53 |  91 | 120 | 153 | 127 | 210 | 129 | 154 |  43 |  13 |\n",
    "| 2001 | 16 | -12 |  46 |  54 |  73 | 128 | 138 | 133 | 176 | 125 | 117 |  55 |  23 |\n",
    "| 2001 | 17 |  -9 |  21 |  26 |  52 |  89 | 123 | 143 | 157 | 109 | 138 |  83 |  20 |\n",
    "| 2001 | 18 |  -5 |   3 |  28 |  44 | 101 | 128 | 149 | 167 | 124 | 147 |  73 |  38 |\n",
    "| 2001 | 19 |  -8 |  26 |  20 |  54 | 119 | 160 | 137 | 181 | 129 | 113 |  63 |  48 |\n",
    "| 2001 | 20 |  -5 |  36 |  22 |  54 | 130 | 156 | 146 | 169 | 138 | 135 |  67 |  26 |\n",
    "| 2001 | 21 |  13 |  62 |  26 |  52 | 129 | 140 | 153 | 159 | 116 | 104 |  96 |  41 |\n",
    "| 2001 | 22 |  45 |  73 |  60 |  64 | 131 | 139 | 169 | 179 | 119 | 118 |  91 |  12 |\n",
    "| 2001 | 23 |  69 |  49 |  77 |  84 | 150 | 160 | 170 | 179 | 103 | 137 |  48 |  18 |\n",
    "| 2001 | 24 |  69 |  27 |  68 |  68 | 157 | 173 | 155 | 191 | 127 | 130 |  68 |  34 |\n",
    "| 2001 | 25 |  48 |  14 |  49 |  85 | 137 | 191 | 181 | 193 | 123 | 124 | 115 |  45 |\n",
    "| 2001 | 26 |  47 |  27 |  38 |  90 | 157 | 219 | 184 | 171 | 112 | 124 |  51 |  27 |\n",
    "| 2001 | 27 |  40 |  27 |  50 |  84 | 160 | 187 | 194 | 152 | 145 | 122 |  48 |  40 |\n",
    "| 2001 | 28 |  11 |  23 |  70 |  99 | 176 | 170 | 206 | 143 | 166 | 116 |  76 |  43 |\n",
    "| 2001 | 29 |  20 |-999 |  66 |  76 | 160 | 177 | 230 | 149 | 168 | 106 |  96 |  18 |\n",
    "| 2001 | 30 |  25 |-999 |  83 |  80 | 149 | 170 | 201 | 154 | 141 | 140 | 124 |  15 |\n",
    "| 2001 | 31 |  40 |-999 | 100 |-999 | 130 |-999 | 192 | 146 |-999 | 101 |-999 |  -4 |\n",
    "| 2002 |  1 | -18 |  86 |  45 | 113 |  93 | 143 | 131 | 161 | 126 | 145 | 114 |  87 |\n",
    "| 2002 |  2 | -18 | 109 |  31 | 100 |  84 | 162 | 135 | 156 | 140 | 156 | 114 |  83 |\n",
    "+------+----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+\n",
    "```\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "library(tidyverse)\n",
    "library(DBI)\n",
    "library(getPass)\n",
    "drv <- switch(Sys.info()['sysname'],\n",
    "             Windows=\"PostgreSQL Unicode(x64)\",\n",
    "             Darwin=\"/usr/local/lib/psqlodbcw.so\",\n",
    "             Linux=\"PostgreSQL\")\n",
    "con <- dbConnect(\n",
    "  odbc::odbc(),\n",
    "  driver = drv,\n",
    "  Server = \"localhost\",\n",
    "  Database = \"sqlzoo\",\n",
    "  UID = \"postgres\",\n",
    "  PWD = getPass(\"Password?\"),\n",
    "  Port = 5432\n",
    ")\n",
    "options(repr.matrix.max.rows=20)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Days, Months and Years\n",
    "The units are 10th of a degree Celcius. The columns are yr and dy for year and day of month. The next twelve columns are for January through to December.\n",
    "\n",
    "**Show the average daily temperature for August 10th 1964**\n",
    "\n",
    "```sql\n",
    "show create table hadcet\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. preteen Dickens\n",
    "\n",
    "Charles Dickens is said to be responsible for the tradition of expecting snow at Christmas [Daily Telegraph](http://www.telegraph.co.uk/topics/christmas/3933091/Dreaming-of-a-white-Christmas-Blame-the-nostalgia-of-Charles-Dickens-snowy-childhood.html). Show the temperature on Christmas day (25th December) for each year of his childhood. He was born in February 1812 - so he was 1 (more or less) in December 1812.\n",
    "\n",
    "**Show the twelve temperatures.**\n",
    "\n",
    "```sql\n",
    "SELECT yr-1811 as age FROM hadcet\n",
    "  WHERE yr BETWEEN 1812 and 1812+12 AND dy=25\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Minimum Temperature Before Christmas\n",
    "\n",
    "We declare a White Christmas if there was a day with an average temperature below zero between 21st and 25th of December.\n",
    "\n",
    "**For each age 1-12 show which years were a White Christmas. Show 'White Christmas' or 'No snow' for each age.**\n",
    "\n",
    "```sql\n",
    "SELECT yr-1811 as age FROM hadcet\n",
    "  WHERE yr BETWEEN 1812 and 1812+12 AND dy=25\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. White Christmas Count\n",
    "\n",
    "A person's White Christmas Count (wcc) is the number of White Christmases they were exposed to as a child (between 3 and 12 inclusive assuming they were born at the beginning of the year and were about 1 year old on their first Christmas).\n",
    "\n",
    "Charles Dickens's wcc was 8.\n",
    "\n",
    "**List all the years and the wcc for children born in each year of the data set. Only show years where the wcc was at least 7.**\n",
    "\n",
    "```sql\n",
    "SELECT yr-1811 as age FROM hadcet\n",
    "  WHERE yr BETWEEN 1812 and 1812+12 AND dy=25\n",
    "```\n",
    "\n",
    "```\n",
    "+------+-----+\n",
    "| yob  | wcc |\n",
    "+------+-----+\n",
    "| 1805 |   7 |\n",
    "| 1806 |   7 |\n",
    "| 1807 |   7 |\n",
    "| 1808 |   8 |\n",
    "| 1809 |   9 |\n",
    "| 1810 |   8 |\n",
    "| 1811 |   8 |\n",
    "| 1812 |   8 |\n",
    "| 1813 |   7 |\n",
    "+------+-----+\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Climate Change\n",
    "\n",
    "Here are the average temperatures for August by decade. You decide."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
